package com.xinlu.derek.common;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;

import com.mysql.jdbc.PreparedStatement;

public class UseDB extends HttpServlet {

	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		
		SAXReader sr = new SAXReader();
		
		try {
			
			Document doc = sr.read(new File(this.getServletContext().getRealPath("/WEB-INF/classes") + "/applicationContext-DB.xml"));
			
			String driverClass = null;
			String jdbcUrl = null;
			String user = null;
			String password = null;
			Connection dbConn;
			
			String sql = null;
			
			if (StringUtils.isNotEmpty(req.getParameter("testSql"))) {
				
				sql = req.getParameter("testSql");
			}else{
				
				sql = " select * from bank_back";
			}
			
			
			List<Node> beanList = doc.selectNodes("//beans/bean");
			
			for (Node node : beanList) {
				
				if ("dataSource".equals(node.valueOf("@id"))) {
					
					List<Node> noList = node.selectNodes("property");
					
					for (Node nd : noList) {
						
						if ("driverClass".equals(nd.valueOf("@driverClass"))) {
							driverClass = nd.valueOf("@value");
						}
						if ("jdbcUrl".equals(nd.valueOf("@jdbcUrl"))) {
							driverClass = nd.valueOf("@value");
						}
						if ("user".equals(nd.valueOf("@user"))) {
							driverClass = nd.valueOf("@value");
						}
						if ("password".equals(nd.valueOf("@password"))) {
							driverClass = nd.valueOf("@value");
						}
						
					}
				}
			}
			
			Class.forName(driverClass).newInstance();
			
			dbConn = DriverManager.getConnection(jdbcUrl,user,password);
			
			PreparedStatement ps = (PreparedStatement) dbConn.prepareStatement(sql);
			
			ps.execute();
			
			
		} catch (DocumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		super.service(req, resp);
	}
	
	public static void main(String[] args) throws DocumentException {
		
		SAXReader sr = new SAXReader();
		
			
		Document doc = sr.read(new File("D:/applicationContext-DB.xml"));
		
		List<Node> beanList = doc.selectNodes("//beans/bean");
		
		for (Node node : beanList) {
			
			if ("dataSource".equals(node.valueOf("@id"))) {
				
				List<Node> noList = node.selectNodes("property");
				
				for (Node nd : noList) {
					
					System.out.println(nd.valueOf("@value"));
					
				}
			}
		}
		
		//List<Node> noList = dataNode.selectNodes("property");
	}
	
	
	
}
